# -*- coding: UTF-8 -*-
import datetime
import json
import math
import os
import sys
import time
import traceback

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)
from src.base import Consts
from itertools import groupby
from operator import itemgetter
from tqdm import tqdm
from src.db import AbsSqlServer
from src.db.AbsSqlServer import AbsSqlServerDev
from src.tasks.regionSpread.CustomJsonEncoder import toJson
from src.utils import BatchUtils, CommonUtils
from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.ProdDb import ProdDb
from src.utils.LogUtils import Logger
from src.db.FCDB import FCDB

logger = Logger()


# 产业单券利差跑批
class RegionInterestRateSpreadIndustry:
    def __init__(self, logger, localDb, dataCenterDb, absSqlServer):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.arg = sys.argv
        self.fcdb = FCDB(logSql=False, autocommit=True)
        self.absSqlServer = absSqlServer
        # self.cursor = AbsSqlServer.Conn.conn.cursor()
        print()

    # 查询行政区划列表
    # 用来匹配债券所属省市区
    def queryRegions(self):
        logger.info("读取全部区域列表")
        sql = """
                       SELECT * from regions 
                    where release_status =1 and release_status =1 and ver=0
        """
        query = self.localDb.Query(sql)
        dataMap = {

        }
        for item in query:
            dataMap.update({
                item['code']: item
            })
        logger.info("读取全部区域列表结束")
        return dataMap

    # 查询城投企业名单 deleted
    def queryPlatformEntList(self):
        sql = """
            SELECT
        a.*,
        a.credit_code,
        d.NAME,
        REPLACE ( b.VALUE , '.0', '' ) AS regionCode,
        e.value AS isLead
        FROM
        enterprise_info_custom d
        left JOIN city_investment_companies a ON d.credit_code = a.credit_code
        left JOIN city_investment_companies_data_detail b ON a.id = b.foreign_id
        and b.second_code='CTJC002001'
        left JOIN city_investment_companies_data_detail e ON a.id = e.foreign_id
        and e.second_code='CTJC002003'
        left JOIN city_investment_company_targets c ON b.second_code = c.second_code
        WHERE
        a.ver = 0
        AND a.release_status = 1
        AND a.`status` = 1
        AND d.release_status = 1
        and a.ver=0
        AND d.ver = 0
        AND d.type_code = 'T001'
        AND d.audit_status = 1

        GROUP BY
        a.credit_code
        """
        return self.dataCenterDb.Query(sql)

    # 同步债务违约标签
    # 获取主体最小违约日期
    def syncDefaultBySecurities(self):
        sql = """
            select temp.compname, temp.COMPCODE as compCode,min(temp.date) as 'date' from (
                select  tci.compname,tbd.COMPCODE,tbd.PFPAYDATE as 'date' 
                from tq_bd_dfltinfo tbd
                left join tq_comp_info tci on tbd.COMPCODE=tci.COMPCODE

                union all
                select tci.compname,cc.ISSUECOMPCODE as COMPCODE,DEFCASHDATE as 'date'  from tq_bd_deferinfo tbd
                left join TQ_BD_NEWESTBASICINFO cc on cc.SECODE =tbd.SECODE
                 left join tq_comp_info tci on cc.ISSUECOMPCODE=tci.COMPCODE
                  
            ) as temp
            group by temp.COMPCODE

        """
        query = self.fcdb.Query(sql, None)
        dataMap = {

        }
        for item in query:
            dataMap.update({
                item['compCode']: item['date']
            })
        return dataMap

    # 获取所有债券数据
    def getAllBonds(self):
        logger.info("（产业）读取城投债券列表")
        sql = """
                SELECT
                    ddb.secode as 'SECODE',
                    ddb.deb_name as 'BONDNAME',
                    ddb.short_name as 'BONDSNAME',
                    ddb.market as 'EXCHANGE',
                    ddb.issuer_code as 'credit_code',
                    ddb.enterprise_name,
                    is_guarantee as 'ISGUARANTEE',
                    ddb.deb_time as 'MATURITYYEAR',
                    ddb.`scale`  as 'ACTISSAMT',
                    ddb.issuing_method  as 'RAISEMODE',
                    ddb.deb_time_option as 'CVTBDEXPIREMEMP',
                    new_rate as 'NEWRATE',
                    ee.region_code,
                    ddb.issue_comp_code as 'ISSUECOMPCODE',
                    ee.ccx_industry_code,
                    ddb.security_id,
                    ee.nature,
                    ee.qe_eval,
                    ddb.deb_type,
                    ddb.rate_type  
                from
                    deb_debenture_base ddb
                    left join ent_enterprise ee on ee.comp_code =ddb.issue_comp_code 
                    where ee.ent_type ='T001'
        """
        return self.localDb.Query(sql)

    # 获取指定条件下债券交易数据总数
    def getYIELDTableCount(self, date, endDate):
        sql = """
            select count(1) as 'total' from TQ_QT_CBESTIMATE
            where  DATASOURCE=1
            and TRADEDATE >= %s
        """
        if endDate is not None:
            sql += " and TRADEDATE<=" + endDate
        return self.fcdb.QueryOne(sql, (date))

    # 查询财汇收益率数据
    def getYIELDTable(self, seCode, date, endDate):
        sql = """
            select TRADEDATE,SECODE,EXCHANGE,VALUATIONTYPE,TERMTOMATURITY,YIELD from TQ_QT_CBESTIMATE
            where SECODE=%s
            and DATASOURCE=1
            and TRADEDATE >= %s
        """
        if endDate is not None:
            sql += " and TRADEDATE<=" + endDate
        return self.fcdb.Query(sql, (seCode, date))

    # 查询指定条件下的收益率数据
    # 收益率曲线 用来计算利差（线性插值好像）
    def getYieldcurve(self, startDate):
        sql = """
            select 
            tqy.YCURVECODE,
                           tqy.TRADEDATE,
                           tqy.YCURVETYPE,
                           tqy.MATURITY,
                           tqy.YIELD
            
            from TQ_QT_YIELDCURVE tqy
            where TRADEDATE =%s and tqy.MATURITY >0 and tqy.YCURVETYPE=1 and tqy.YCURVECODE=269
            order BY tqy.MATURITY
        """
        content = None
        # 收益率曲线数据保存到本地
        filePath = Consts.yieldcurve_path + startDate + ".txt"
        try:
            with open(filePath, "r") as file:
                content = file.read()
            if content is not None:
                return json.loads(content)
            else:
                logger.info("读取收益率曲线文件失败" + filePath + "：，文件内容为空")
        except FileNotFoundError:
            logger.info("读取收益率曲线失败，" + filePath + "文件不存在")
            pass
        logger.info("（产业）查询收益率曲线" + str(startDate) + "数据")
        query = self.fcdb.Query(sql, startDate)
        logger.info("（产业）查询收益率曲线数据，条数：" + str(len(query)))
        if len(query) > 0:
            self.saveFile(query, filePath)
        return query

    # 收益率曲线数据保存到本地
    def saveFile(self, data, filePath):
        logger.info("开始保存收益率曲线JSON文件" + str(filePath))
        with open(filePath, 'w') as f:  # 如果filename不存在会自动创建， 'w'表示写数据，写之前会清空文件中的原有数据！
            f.write(toJson(data))
        logger.info("保存收益率曲线JSON文件完成")

    # 获取符合条件的secode
    # 根据不同的交易市场获取不同的secode
    def getSeCode(self, itemByBondName: list):
        # 只有一条，直接取
        if len(itemByBondName) == 1:
            return itemByBondName[0]
        else:
            dataMap = {

            }
            for item in itemByBondName:
                dataMap.update({
                    item['EXCHANGE']: item
                })
            if dataMap.__contains__('001005'):
                return dataMap.get("001005")

            if dataMap.__contains__('001002'):
                return dataMap.get("001002")

            if dataMap.__contains__('001003'):
                return dataMap.get("001003")

            if dataMap.__contains__('001006'):
                return dataMap.get("001006")
            if dataMap.__contains__('001018'):
                return dataMap.get("001018")
            return None

    # 保存产业数据到本地数据库
    def __updateLocal__(self, updateDatas):
        sql = """
                    INSERT INTO Fits2Test.dbo.base_bonds_interest_rate_spread_industry_new
                     (secode, bond_name, curve_yield, comp_code, trade_date, cbestimate_yield, interest_rate_spread,
                      is_guarantee, maturity_year, actissamt, raise_mode, cvtbdexpirememp, new_rate, region_code
                      , province_code, city_code, county_code, [type], [level], functional_code,termtomaturity,credit_code,short_name,ccx_industry_code,security_id,
                      nature,rating_rank,rating_rank_search,tag) 
                    VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
        """
        # return self.localDb.ExecMany(sql, updateDatas)
        many = self.absSqlServer.execute_many(sql, updateDatas)
        return many

    # 删除指定日期的利差数据
    def deleteDataByDate(self, date, endDate):
        sql = """
         DELETE FROM Fits2Test.dbo.base_bonds_interest_rate_spread_industry_new WHERE  trade_date >= %s
        """
        if endDate is not None:
            sql += " and trade_date<=" + endDate
        if date is not None:
            many = self.absSqlServer.execute_non_query(sql, date)
            return many

    def saveData(self, updateData: list, dbMainResultList: list):
        if updateData is None and len(updateData) == 0:
            return
        batchList = []
        for r in updateData:
            batchList.append((
                r.get("secode"),
                r.get("bondName"),
                r.get("curve_yield"),
                r.get("compCode"),
                r.get("tradeDate"),
                r.get("cbestimateYield"),
                r.get("interest_rate_spread"),
                r.get("ISGUARANTEE"),
                r.get("MATURITYYEAR"),
                r.get("ACTISSAMT"),
                r.get("RAISEMODE"),
                r.get("CVTBDEXPIREMEMP"),
                r.get("NEWRATE"),
                r.get("region_code"),
                r.get("province_code"),
                r.get("city_code"),
                r.get("county_code"),
                r.get("type"),
                r.get("level"),
                r.get("functional_code"),
                r.get("termtomaturity"),
                r.get("credit_code"),
                r.get("shortName"),
                r.get("ccx_industry_code"),
                r.get("security_id"),
                r.get("nature"),
                r.get("qe_eval"),
                r.get("qe_eval_search"),
                r.get("tag")
            ))
        updatedCount = BatchUtils.batchInsert(self.__updateLocal__, batchList, 20000)
        return

    # 统一修复单券利差的tag
    def fixTag(self, allDefaultData, bondsAll):
        # 根据债券全称分组，判断exchange
        # 排序
        bondsAll.sort(key=itemgetter("BONDNAME"))
        bondsAll = groupby(bondsAll, key=itemgetter("BONDNAME"))
        for bondName, items in bondsAll:
            # 获取唯一secode
            itemByBondName = list(items)
            bondsInfo = self.getSeCode(itemByBondName)
            if bondsInfo is None:
                continue
            seCode = bondsInfo['SECODE']
            credit_code = bondsInfo['credit_code']
            compCode = bondsInfo['ISSUECOMPCODE']
            # 打标签
            tag = 1
            # 债券类型
            deb_type = bondsInfo['deb_type']
            # 计息方式
            rate_type = bondsInfo['rate_type']

            if deb_type == '622' or deb_type == '623' or deb_type == '624':
                tag = 2
            if rate_type != 20:
                tag = 3
            logger.info("========更新单券Tag" + seCode + " ========")
            self.updateTag(tag, "secode", seCode)
        for compCode in allDefaultData:
            date = allDefaultData[compCode]
            # logger.info("========更新主体tag" + compCode + " ========")
            self.updateTagByCompCode(4, date, compCode)
        sys.exit(0)
        pass

    def updateTag(self, tag, filed, condition):
        sql = """
        UPDATE Fits2Test.dbo.base_bonds_interest_rate_spread_industry_new SET tag=%s WHERE secode=%s;
            
        """
        self.absSqlServer.execute_non_query(sql, (tag, condition))

    def updateTagByCompCode(self, tag, date, compCode):
        sql = """
        UPDATE Fits2Test.dbo.base_bonds_interest_rate_spread_industry_new SET tag=%s WHERE
         comp_code=%s and trade_date >= %s

        """
        update = self.absSqlServer.execute_non_query(sql, (tag, compCode, date))
        if update > 0:
            logger.info(
                "========更新主体tag  企业CODE：" + compCode + " 交易日大于等于" + date + "的产业利差数据" + str(
                    update) + "条========")

    # 一、利差计算

    # 1.选取所有城投企业债券
    # 2.根据债券全称去重，EXCHANGE优先银行间(001005)，其次交易所(001002或001003)，再次固定收益平台(001006或001018)，最后其他
    # 3.选择收益率YIELD(TQ_QT_CBESTIMATE),优先选择VALUATIONTYPE=1，如果没有则选择VALUATIONTYPE=2，如果VALUATIONTYPE=1或2时有两条数据则任选一条；并且DATASOURCE=1
    # 4.选取债券剩余年限(TERMTOMATURITY)、国开债收益率曲线剩余期限(MATURITY),线性插值计算YIELD(TQ_QT_YIELDCURVE,YCURVETYPE=1)
    # 5.债券利差计算   YIELD(TQ_QT_CBESTIMATE)-YIELD(TQ_QT_YIELDCURVE,YCURVETYPE=1)
    # 6.城投/区域利差计算——中位数或发行额加权平均:实际发行额ACTISSAMT(TQ_BD_NEWESTBASICINFO)
    def process(self):
        dateByCommand = CommonUtils.isCommandParam(self.arg, "date")
        endDateByCommand = CommonUtils.isCommandParam(self.arg, "endDate")
        typeByCommand = CommonUtils.isCommandParam(self.arg, "type")
        # 判断是否有指定日期参数
        # 开始交易日
        if dateByCommand['exist']:
            date = dateByCommand["value"]
            yesterday = datetime.datetime.strptime(date, '%Y%m%d') - datetime.timedelta(days=1)
        else:
            today = datetime.date.today()
            yesterday = today - datetime.timedelta(days=1)
            date = str(yesterday).replace("-", "")
        logger.info("（产业）==============日期:" + date)
        # 参数
        # 结束交易日
        endDate = None
        if endDateByCommand['exist']:
            endDate = endDateByCommand["value"]

        # 获取全部违约数据
        allDefaultData = self.syncDefaultBySecurities()
        # 获取全部债券
        bondsAll = self.getAllBonds()
        type = None
        if typeByCommand['exist']:
            type = typeByCommand["value"]
        # 统一修复tag
        # 不同tag代表不同含义
        # 修复完直接退出程序
        if type == 'fixtag':
            self.fixTag(allDefaultData, bondsAll)

        self.deleteDataByDate(date, endDate)

        # 查询全部区域
        regionsAll = dict(self.queryRegions())

        logger.info("（产业）债券数量：" + str(len(bondsAll)))

        # yieldCount = self.getYIELDTableCount(date, endDate)
        #
        # logger.info("（产业）指定交易日交易数据条数：" + str(yieldCount['total']))

        # 根据债券全称分组，判断exchange
        # 排序
        bondsAll.sort(key=itemgetter("BONDNAME"))

        # 对债券全程字段分组汇总
        logger.info("（产业）根据债券全程进行分组")
        bondsAll = groupby(bondsAll, key=itemgetter("BONDNAME"))
        logger.info("（产业）循环计算每个债券的利差")

        count1 = 0
        count2 = 0
        count3 = 0

        try:
            for bondName, items in bondsAll:
                # 需要保存的数据
                dbResultList = []
                itemByBondName = list(items)
                # 获取唯一secode
                bondsInfo = self.getSeCode(itemByBondName)
                if bondsInfo is None:
                    count2 += 1
                    continue
                seCode = bondsInfo['SECODE']
                credit_code = bondsInfo['credit_code']
                shortName = bondsInfo['BONDSNAME']
                if seCode is None:
                    continue
                # SECODE拿到后抽取收益率数据
                yield_table = self.getYIELDTable(seCode, date, endDate)

                # 收益率数据缺失，跳过
                if len(yield_table) == 0:
                    # tqdm.write(bondName + "未查询到收益率数据，跳过。")
                    # logger.info(bondName + "收益率曲线表缺失，跳过")
                    count3 += 1
                    continue
                # 所属区域CODE
                # logger.info("计算债券" + bondName + "的利差")
                logger.info(seCode + "（产业）拉取大于等于" + date + "并小于等于" + (
                    " " if endDate is None else endDate) + "的交易数据，条数：" + str(len(yield_table)))
                region_code = bondsInfo['region_code']

                for yield_item in yield_table:
                    # 交易日
                    tradeDate = yield_item['TRADEDATE']
                    # 剩余期限
                    termtomaturity = yield_item['TERMTOMATURITY']
                    yieldData = yield_item['YIELD']
                    yieldcurveList = self.getYieldcurve(tradeDate)
                    if len(yieldcurveList) == 0:
                        # tqdm.write(bondName + "收益率曲线表缺失，跳过")
                        count1 += 1
                        continue
                    spread = self.calSpread(termtomaturity, yieldData, yieldcurveList)
                    CVTBDEXPIREMEMP = bondsInfo['CVTBDEXPIREMEMP']
                    RAISEMODE = bondsInfo['RAISEMODE']
                    if (CVTBDEXPIREMEMP is not None and CVTBDEXPIREMEMP.__contains__("N")):
                        RAISEMODE = 3
                    qe_eval = bondsInfo['qe_eval']
                    qe_eval_search = None
                    if qe_eval is not None:
                        qe_eval_search = str(qe_eval).replace('+', '').replace('-', '')

                    tag = self.isValidFunc(allDefaultData, bondsInfo['ISSUECOMPCODE'], yesterday, bondsInfo,
                                           termtomaturity)

                    dbResult = {
                        "compCode": bondsInfo['ISSUECOMPCODE'],
                        "curve_yield": spread,
                        "cbestimateYield": yieldData,
                        "interest_rate_spread": None if spread is None else float(yieldData) - float(spread),
                        "tradeDate": tradeDate,
                        "secode": seCode,
                        "bondName": bondName,
                        "ISGUARANTEE": bondsInfo['ISGUARANTEE'],
                        "termtomaturity": termtomaturity,
                        "MATURITYYEAR": bondsInfo['MATURITYYEAR'],
                        "ACTISSAMT": bondsInfo['ACTISSAMT'],
                        "RAISEMODE": RAISEMODE,
                        "CVTBDEXPIREMEMP": CVTBDEXPIREMEMP,
                        "NEWRATE": bondsInfo['NEWRATE'],
                        "region_code": region_code,

                        "credit_code": credit_code,
                        "shortName": shortName,
                        "ccx_industry_code": bondsInfo['ccx_industry_code'],
                        "security_id": bondsInfo['security_id'],
                        "nature": bondsInfo['nature'],
                        "qe_eval": qe_eval,
                        "qe_eval_search": qe_eval_search,
                        "tag": tag
                    }

                    if (regionsAll.get(region_code) != None):
                        regionInfo = {
                            "province_code": regionsAll.get(region_code)['province_code'],
                            "city_code": regionsAll.get(region_code)['city_code'],
                            "county_code": regionsAll.get(region_code)['county_code'],
                            "type": regionsAll.get(region_code)['type'],
                            "level": regionsAll.get(region_code)['level'],
                        }
                        dbResult.update(regionInfo)

                    dbResultList.append(dbResult)
                self.saveData(dbResultList, None)
        except Exception as e:
            logger.info("（产业利差）计算失败，错误信息：" + str(e))
            logger.info(traceback.format_exc())

        logger.info("（产业）收益率曲线表缺失:" + str(count1) + "次")
        logger.info("（产业）收益率SECODE缺失:" + str(count2) + "次")
        logger.info("（产业）交易日数据缺失:" + str(count3) + "次")

    def isValidFunc(self, defaultDataMap, compCode, currentDate, bondsInfo, termtomaturity):
        # -- 剔除所有可转债(BONDTYPE2=622、623)、可交债(BONDTYPE2=624),TQ_BD_NEWESTBASICINFO
        #                     and (deb_type !=622  and deb_type !=623 and deb_type !=624)
        #                     -- 选取计息方式为固定利率债券(CALCAMODE=20)
        #                     and ddb.rate_type =20
        # 打标签
        tag = 1
        # 债券类型
        deb_type = bondsInfo['deb_type']
        # 计息方式
        rate_type = bondsInfo['rate_type']

        if deb_type == '622' or deb_type == '623' or deb_type == '624':
            tag = 2
        if rate_type != 20:
            tag = 3
        if termtomaturity is not None and termtomaturity < 0.5:
            tag = 5
        if compCode is not None:
            if defaultDataMap.__contains__(compCode):
                date = defaultDataMap.get(compCode)
                if str(currentDate).replace("-", "") >= date:
                    # 处于违约日之后 ,打上无效标签
                    return 4
        return tag

    # 计算力差
    def calSpread(self, termtomaturity, yieldData, yieldcurveList):
        for index, curveItem in enumerate(yieldcurveList):
            maturity_ = float(curveItem['MATURITY'])
            yield_ = float(curveItem['YIELD'])
            if index == len(yieldcurveList) - 1:
                break
            if termtomaturity == maturity_:
                return float(yieldData) - float(yield_)

            if (termtomaturity > maturity_) and (termtomaturity < yieldcurveList[index + 1]['MATURITY']):
                T1MATURITY = float(yieldcurveList[index + 1]['MATURITY'])
                T1YIELD = float(yieldcurveList[index + 1]['YIELD'])
                # 2.540105
                # Y=Y1+ (Y2-Y1)× (X-X1)/ (X2-X1
                # 收益率1 = yield_ + (T1YIELD - yield_) * (yieldData - maturity_) / (T1MATURITY - maturity_)
                收益率 = (float(termtomaturity) - maturity_) / (T1MATURITY - maturity_) * (T1YIELD - yield_) + yield_
                return 收益率
        return None


# python RegionInterestRateSpreadIndustry.py --date=20200101 --endDate=20201231
# python RegionInterestRateSpreadIndustry.py --date=20210101 --endDate=20211231
# python RegionInterestRateSpreadIndustry.py --date=20220101 --endDate=20221231
# python RegionInterestRateSpreadIndustry.py --date=20230101 --endDate=20231231
# python RegionInterestRateSpreadIndustry.py --date=20240101 --endDate=20240715
if __name__ == "__main__":
    logger = Logger()
    localDb = ProdDb(logSql=False, autocommit=True)
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    absSqlServer = AbsSqlServerDev()
    t = time.perf_counter()
    logger.info("========计算单券利差（产业）数据 开始 ========")
    task = RegionInterestRateSpreadIndustry(logger=logger, localDb=localDb, dataCenterDb=dataCenterDb,
                                            absSqlServer=absSqlServer)
    task.process()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))
    logger.info("========计算单券利差（产业）数据 结束 ========")
